import os
import threading
import pandas as pd
import numpy as np
import openpyxl
from django.db.models import Sum, Max
import random
from DataPlatform.settings import STATIC_ROOT
from business.models import CalResult, DesignPlan, ConfigLimit
from business.models import ICE, GasBoiler, ElectricBoiler, AR, ERU, AirPump, GroundPump, GasTurbine, \
    WindPower, PVP, ARS, Buttery, IceTank
import cplex
from business.helper import get_device


class PSO(object):
    def __init__(self, config: DesignPlan, mark: str, who: str, prc: list):
        """
        :param config:配置信息
        :param mark: 计算编号
        :param who: 计算创人者账号
        """
        self.prc = prc  # 计算进度列表:0-->进度，1-->信息，2-->收敛曲线（数组）
        self.mark = mark
        self.who = who
        self.config = config  # 用户配置方案表

        # 下层优化参数
        self.m = 8760  # 全年小时数
        self.n = 23  # 运行优化的变量数
        self.d = 365  # 全年天数
        self.set_I = range(1, self.n + 1)  # 用于逐项变量循环的范围
        self.set_T = range(1, self.m + 1)  # 用于全年按小时循环的范围
        self.prob = cplex.Cplex()  # 内层问题求解
        self.res = 0  # 结果
        self.result = 0  # 结果

        #  读取excel
        data_file = os.path.join(STATIC_ROOT + '/business/user_params_file/', self.who + '_load_' + self.mark + '.xlsx')
        wind_file = os.path.join(STATIC_ROOT + '/business/user_params_file/', self.who + "_wind_" + self.mark + ".xlsx")
        light_file = os.path.join(STATIC_ROOT + '/business/user_params_file/',
                                  self.who + "_light_" + self.mark + ".xlsx")
        self.load_params = pd.read_excel(data_file, sheet_name=0).rename(
            columns={"日期": "date", "小时": "hour", "热负荷（kW）": "heat", "冷负荷（kW）": "cold", "电负荷（kW）": "power",
                     "气负荷(kW)": "gas"})
        self.date = self.load_params['date'].values
        self.hour = self.load_params['hour'].values
        self.lh = self.load_params['heat'].values
        self.lc = self.load_params['cold'].values
        self.le = self.load_params['power'].values
        self.lg = self.load_params['gas'].values

        self.price_buy = pd.read_excel(data_file, sheet_name=1).rename(
            columns={"电价（元/kWh）": "power", "气价（元/kWh）": "gas", "热价（元/kWh）": "heat"})
        self.ele_pri = self.price_buy['power'].values
        self.gas_pri = self.price_buy['gas'].values
        self.heat_pri = self.price_buy['heat'].values
        self.epr = np.tile(self.ele_pri, self.d).reshape(self.m)
        self.gpr = np.tile(self.gas_pri, self.d).reshape(self.m)
        self.hpr = np.tile(self.heat_pri, self.d).reshape(self.m)

        self.price_sell = pd.read_excel(data_file, sheet_name=2).rename(
            columns={"电（元/kWh）": "power", "气（元/kWh）": "gas", "热（元/kWh）": "heat", "冷（元/kWh）": "cold"})
        self.ele_sell = np.tile(self.price_sell['power'].values, self.d).reshape(self.m)
        self.gas_sell = np.tile(self.price_sell['gas'].values, self.d).reshape(self.m)
        self.heat_sell = np.tile(self.price_sell['heat'].values, self.d).reshape(self.m)
        self.cool_sell = np.tile(self.price_sell['cold'].values, self.d).reshape(self.m)

        self.price_sell_net = pd.read_excel(data_file, sheet_name=3).rename(columns={"电（元/kWh）": "power"})
        self.psn = np.tile(self.price_sell_net['power'].values, self.d).reshape(self.m)

        self.wind_resource = pd.read_excel(wind_file).rename(
            columns={"平均风速（m/s）": "wind", "时刻": "time"}) if self.config.wind_is_selected else None
        self.wind = self.wind_resource['wind'].values if self.config.wind_is_selected else np.zeros(self.m)

        self.light_resource = pd.read_excel(light_file).rename(
            columns={"实际辐射照度（W/m2）": "light", "时刻": "time"}) if self.config.solar_is_selected else None
        self.pv = self.light_resource['light'].values if self.config.solar_is_selected else np.zeros(self.m)

        # PSO相关参数
        self.w = config.inertia_weight  # 惯性权重
        self.c1 = config.c1  # 个体学习因子
        self.c2 = config.c2  # 社会学习因子
        self.pop = int(config.pop_size)  # 种群规模
        self.dim = 16  # 维度
        self.max_iter = int(config.iter_max_num)  # 最大迭代次数
        self.iter = 0  # 当前迭代次数
        self.min_w = 0.2  # 最小惯性权重
        self.nsw = self.w - self.iter * (self.w - self.min_w) / self.max_iter  # 惯性递减
        self.X = np.zeros((self.pop, self.dim))  # 种群位置
        self.V = np.zeros((self.pop, self.dim))  # 飞行速度

        # 设备信息
        self.SLG_ICE = get_device(ICE, config, 'ice_which', 'ice_is_selected')  # 内燃机
        self.SLG_gt = get_device(GasTurbine, config, 'gt_which', 'gt_is_selected')  # 燃气轮机
        self.SLG = self.config.cchp_is_selected  # 三联供
        self.ars = get_device(ARS, config, 'ars_which', 'ice_is_selected')  # 与联供系统相关的烟气热水机
        self.gp = get_device(GroundPump, config, 'gp_which', 'gp_is_selected')  # 地源热泵
        self.ap = get_device(AirPump, config, 'ap_which', 'ap_is_selected')  # 风冷热泵
        self.gb = get_device(GasBoiler, config, 'gb_which', 'gb_is_selected')  # 燃气锅炉
        self.ar = get_device(AR, config, 'ar_which', 'ar_is_selected')  # 直燃机
        self.eb = get_device(ElectricBoiler, config, 'eb_which', 'eb_is_selected')  # 电锅炉
        self.wind_power = get_device(WindPower, config, 'wind_which', 'wind_is_selected')  # 风机
        self.pvp = get_device(PVP, config, 'solar_which', 'solar_is_selected')  # 光伏
        self.eru = get_device(ERU, config, 'cu_which', 'cu_is_selected')  # 冷水机
        self.ice_tank = get_device(IceTank, config, 'it_which', 'it_is_selected')  # 冰槽
        self.battery = get_device(Buttery, config, 'battery_which', 'battery_is_selected')  # 蓄电池

        # 三联供机组选择的判断
        if self.config.ice_is_selected:
            self.e5 = 1
            self.g5 = 100 / self.SLG_ICE.power_effect
            self.h5 = self.SLG_ICE.heat_effect / self.SLG_ICE.power_effect
            self.c5 = self.ars.effect * self.SLG_ICE.heat_effect / self.SLG_ICE.power_effect
        elif self.config.gt_is_selected:
            self.e5 = 1
            self.g5 = 1 / self.SLG_gt.generation_effect
            self.h5 = self.SLG_gt.heat_effect / self.SLG_gt.generation_effect
            self.c5 = self.SLG_gt.heat_effect / self.SLG_gt.generation_effect
        else:
            self.e5 = 0
            self.g5 = 0
            self.h5 = 0
            self.c5 = 0

        # 设备容量的设定范围
        max_1 = config.cchp_max_power if self.SLG else 0
        max_2 = config.gp_max_power if self.gp else 0
        max_3 = config.ap_max_power if self.ap else 0
        max_4 = config.gb_max_power if self.gb else 0
        max_5 = config.ar_max_power if self.ar else 0
        max_6 = config.eb_max_power if self.eb else 0
        max_7 = config.ice_storage_max_power_ch if self.eru else 0
        max_8 = config.ice_storage_max_power_dis if self.eru else 0
        max_9 = config.heat_storage_max_powerch if config.heat_storage_is_selected else 0
        max_10 = config.heat_storage_max_powerdis if config.heat_storage_is_selected else 0
        max_11 = config.ele_storage_max_powerch if self.battery else 0
        max_12 = config.wind_count_max if self.wind_power else 0
        max_13 = config.pv_count_max if self.pvp else 0
        max_14 = config.ele_storage_max_capacity if self.battery else 0
        max_15 = config.heat_storage_max_capacity if config.heat_storage_is_selected else 0
        max_16 = config.ice_storage_max_capacity if self.ice_tank else 0

        min_1 = config.cchp_min_power if self.SLG else 0
        min_2 = config.gp_min_power if self.gp else 0
        min_3 = config.ap_min_power if self.ap else 0
        min_4 = config.gb_min_power if self.gb else 0
        min_5 = config.ar_min_power if self.ar else 0
        min_6 = config.eb_min_power if self.eb else 0
        min_7 = config.ice_storage_min_power_ch if self.eru else 0
        min_8 = config.ice_storage_min_power_dis if self.eru else 0
        min_9 = config.heat_storage_min_powerch if config.heat_storage_is_selected else 0
        min_10 = config.heat_storage_min_powerdis if config.heat_storage_is_selected else 0
        min_11 = config.ele_storage_max_powerdis if self.battery else 0
        min_12 = config.wind_count_min if self.wind_power else 0
        min_13 = config.pv_count_min if self.pvp else 0
        min_14 = config.ele_storage_min_capacity if self.battery else 0
        min_15 = config.heat_storage_min_capacity if config.heat_storage_is_selected else 0
        min_16 = config.ice_storage_min_capacity if self.ice_tank else 0

        # 顺序: cchp发电1 地源热泵耗电2 风冷热泵耗电3 燃气锅炉耗气4 直燃机耗气5 电锅炉耗电6 冷机耗电7 融冰功率8
        # 蓄热9 放热10 电池充放11 风机12 光伏13 电池容量14 水箱容量15 冰槽容量16
        self.x_max = np.array([max_1, max_2, max_3, max_4, max_5, max_6, max_7, max_8, max_9, max_10, max_11,
                               max_12, max_13, max_14, max_15, max_16])
        self.x_min = np.array([min_1, min_2, min_3, min_4, min_5, min_6, min_7, min_8, min_9, min_10, min_11,
                               min_12, min_13, min_14, min_15, min_16])

        self.v_max = self.x_max * config.fly_speed  # 速度上限(用户所设置的最大飞行速度占问题空间百分比值与位置边界相乘)
        self.v_min = - self.x_max * config.fly_speed  # 速度下限
        self.p_best = np.zeros((self.pop, self.dim))  # 所有粒子的历史最优
        self.g_best = np.zeros((1, self.dim))  # 全局最优
        self.p_fit = np.zeros(self.pop)  # 所有粒子的历史最优适应度
        self.fit = float(1e10)  # 用于更新全局最优的适应度值
        self.fit_list = []
        self.op_value = float(1e10)  # 初始的运行费用值
        self.fitness_value = float(1e10)
        self.ref = 0
        self.pro = 0  # 用于计算进度
        self.nsl_count = 0  # 统计初始化种群中无解的个数
        self.fit_kp = 0  # 统计适应度值未发生改变次数
        self.error = 0  # 计算残差
        self.err_ct = 0  # 统计残差小于收敛残差的次数
        self.itr_ct = 0

        # 设备投资费用信息
        inv_1 = 0
        if config.cchp_is_selected:
            if config.ice_is_selected:
                inv_1 = self.SLG_ICE.ppp + self.ars.ppp
            elif config.gt_is_selected:
                inv_1 = self.SLG_gt.ppp + self.ars.ppp
        else:
            pass
        inv_2 = self.gp.ppp if self.gp else 0
        inv_3 = self.ap.ppp if self.ap else 0
        inv_4 = self.gb.ppp if self.gb else 0
        inv_5 = self.ar.ppp if self.ar else 0
        inv_6 = self.eb.ppp if self.eb else 0
        inv_7 = self.eru.ppp if self.eru else 0
        inv_8 = 0
        inv_9 = config.heat_storage_investment if config.heat_storage_is_selected else 0
        inv_10 = 0
        inv_11 = self.battery.ppp if self.battery else 0
        inv_12 = self.wind_power.ppp if self.wind_power else 0
        inv_13 = self.pvp.ppp if self.pvp else 0
        inv_14 = 0
        inv_15 = 0
        inv_16 = 0

        self.v = 0
        self.i = 0.07
        self.R = (self.i * ((1 + self.i) ** 20)) / (((1 + self.i) ** 20) - 1)  # 投资折算

        self.p_invest = np.array(
            [inv_1, inv_2, inv_3, inv_4, inv_5, inv_6, inv_7, inv_8, inv_9, inv_10, inv_11, inv_12, inv_13,
             inv_14, inv_15, inv_16])
        self.invest = 0

    def init_pop(self):  # 种群初始化方法
        # 初始粒子群生成
        for i in range(self.pop):
            for j in range(self.dim):
                self.X[i][j] = random.uniform(self.x_min[j], self.x_max[j])  # 设置范围内的随机位置
                self.V[i][j] = random.uniform(self.v_min[j], self.v_max[j])  # 设置范围内的随机速度
                # 计算投资
            if self.config.opt_target == 0:
                self.invest = np.dot(self.p_invest, self.X[i]) * (self.R * (1 - self.v) + self.i * self.v)
            else:
                self.invest = 0
            # 当前位置作为每个粒子的历史最优
            self.p_best[i] = self.X[i]
            # 计算适应度
            tmp = self.opt(self.X[i]) + self.invest
            print('=========tmp=========', tmp)
            if self.opt(self.X[i]) == float(1e10):
                self.nsl_count += 1
            # 更新个体历史最优
            if tmp < self.p_fit[i]:
                self.p_fit[i] = tmp
            # 更新全局最优
            if tmp < self.fit:
                self.fit = tmp.copy()
                self.g_best = self.X[i]  # 更新全局最优适应度
            print('————————————————————-init_gbest———————————————————————-', self.fit)

    def opt(self, x):  # 运行参数优化方法
        # 变量命名
        x_vars = []
        for t in self.set_T:
            x_t = []
            for i in self.set_I:
                xv = 'x_' + str(i) + '_' + str(t)
                x_t.append(xv)
            x_vars.append(x_t)
        x_vars_array = np.array(x_vars)
        x_vars_1d = x_vars_array.reshape(self.m * self.n)
        var = x_vars_1d.tolist()

        # 内层目标
        zr = np.zeros((self.n - 3, self.m))
        lp = min(self.ele_pri)
        zn = np.ones((3, self.m))
        if self.config.opt_target == 0:
            obj = np.vstack((self.epr, self.gpr, self.hpr, zr)).T
        else:
            obj = np.vstack((zn, zr)).T
        obj_list = obj.reshape(self.m * self.n).tolist()

        # 判断系统模式
        if self.config.model_which == 0:  # 离网
            e1 = 0
            e4 = 0
            g2 = 0
            h3 = 0
        elif self.config.model_which == 1:  # 并网不上网
            e1 = 1
            e4 = 0
            g2 = 1
            h3 = 1
        else:  # 并网上网
            e1 = 1
            e4 = 1
            g2 = 1
            h3 = 1

        # 设备参数设置
        e6 = 1 if self.gp else 0
        e7 = 1 if self.gp else 0
        h6 = self.gp.make_heat_effect if self.gp else 0
        c7 = self.gp.make_cool_effect if self.gp else 0
        e8 = 1 if self.ap else 0
        c8 = self.ap.effect if self.ap else 0
        g9 = 1 if self.gb else 0
        h9 = self.gb.heat_effect / 100 if self.gb else 0
        g10 = 1 if self.ar else 0
        c10 = self.ar.cool_effect if self.ar else 0
        e11 = 1 if self.eb else 0
        h11 = self.eb.heat_effect / 100 if self.eb else 0
        h12 = 1 if self.config.heat_storage_is_selected else 0
        h13 = 1 if self.config.heat_storage_is_selected else 0
        e12 = 1 / 0.92 if self.config.heat_storage_is_selected else 0
        e14 = 1 if self.eru else 0
        c14 = self.eru.cool_effect if self.eru else 0
        c15 = 1 if self.ice_tank else 0
        c16 = 1 if self.ice_tank else 0
        e15 = 1 / self.eru.ice_effect if self.ice_tank else 0
        e17 = 1 if self.battery else 0
        e18 = 1 if self.battery else 0

        # 等式约束
        my_row_names = []
        for t in self.set_T:
            cons_e = 'constraint_e_' + str(t)
            my_row_names.append(cons_e)
            cons_g = 'constraint_g_' + str(t)
            my_row_names.append(cons_g)
            cons_h = 'constraint_h_' + str(t)
            my_row_names.append(cons_h)
            cons_c = 'constraint_c_' + str(t)
            my_row_names.append(cons_c)

        # 购电1 购气2 购热3 上网4  cchp供电5 gp供热耗电6  gp供冷耗电7 ap供冷耗电8 gb耗气9 ar耗气10 eb耗电11
        # 蓄热12 放热13 冷水机14 蓄冷15 释冷16 充电17 放电18 风机19 光伏20 电池21 水箱22 冰槽23
        eql_cons_ele = \
            [e1, 0, 0, -e4, self.e5, -e6, -e7, -e8, 0, 0, -e11, -e12, 0, -e14, 0, 0, -e17, e18, 0, 0, 0, 0, 0]
        eql_cons_gas = [0, g2, 0, 0, -self.g5, 0, 0, 0, -g9, -g10, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0]
        eql_cons_heat = [0, 0, h3, 0, self.h5, h6, 0, 0, h9, 0, h11, -h12, h13, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0]
        eql_cons_cool = [0, 0, 0, 0, self.c5, 0, c7, c8, 0, c10, 0, 0, 0, c14, -c15, c16, 0, 0, 0, 0, 0, 0, 0]

        # 根据当前负荷分布修改系数
        rows = []
        for t in self.set_T:
            if self.lc[t - 1] == 0 and self.lh[t - 1] == 0:
                eql_cons_ele[5] = 0
                eql_cons_ele[6] = 0
                eql_cons_heat[5] = 0
                eql_cons_cool[6] = 0
                eql_cons_heat[4] = 0
                eql_cons_cool[4] = 0
            elif self.lc[t - 1] == 0 and self.lh[t - 1] > 0:
                eql_cons_ele[5] = -1 * e6
                eql_cons_ele[6] = 0
                eql_cons_heat[5] = h6
                eql_cons_cool[6] = 0
                eql_cons_cool[4] = 0
                eql_cons_heat[4] = self.h5
            elif self.lc[t - 1] > 0 and self.lh[t - 1] == 0:
                eql_cons_ele[5] = 0
                eql_cons_ele[6] = -1 * e7
                eql_cons_heat[5] = 0
                eql_cons_cool[6] = c7
                eql_cons_heat[4] = 0
                eql_cons_cool[4] = self.c5
            else:
                eql_cons_ele[5] = 0
                eql_cons_ele[6] = -1 * e7
                eql_cons_heat[5] = 0
                eql_cons_cool[6] = c7
                eql_cons_heat[4] = 0
                eql_cons_cool[4] = self.c5

            if self.eru and not self.config.hp_is_selected and not self.config.ar_is_selected:
                if self.epr[t - 1] == lp and self.lc[t - 1] == 0:
                    eql_cons_cool[13] = self.eru.ice_effect if self.eru else 0
                    eql_cons_cool[14] = -1 * c15
                    eql_cons_cool[15] = 0
                else:
                    eql_cons_cool[13] = c14
                    eql_cons_cool[14] = 0
                    eql_cons_cool[15] = c16
            else:
                if self.epr[t - 1] == lp:
                    eql_cons_cool[13] = self.eru.ice_effect if self.eru else 0
                    eql_cons_cool[14] = -1 * c15
                    eql_cons_cool[15] = 0
                else:
                    eql_cons_cool[13] = c14
                    eql_cons_cool[14] = 0
                    eql_cons_cool[15] = c16

            # 根据上传数据处理风机与光伏的相应系数
            target_wind_level = int(self.wind[t - 1])
            target_wind_level_str = str(target_wind_level)
            eql_cons_ele[18] = getattr(self.wind_power, 's' + target_wind_level_str) \
                if 2 <= target_wind_level <= 25 and self.wind_power else 0
            eql_cons_ele[19] = self.pvp.max_power / 1000 * self.pv[t - 1] / 1000 if self.pvp else 0

            row_e = [x_vars[t - 1], eql_cons_ele.copy()]
            rows.append(row_e)
            row_g = [x_vars[t - 1], eql_cons_gas.copy()]
            rows.append(row_g)
            row_h = [x_vars[t - 1], eql_cons_heat.copy()]
            rows.append(row_h)
            row_c = [x_vars[t - 1], eql_cons_cool.copy()]
            rows.append(row_c)
        my_sense = ['E'] * (self.m * 4)

        # 负荷
        load = np.vstack((self.le, self.lg, self.lh, self.lc)).T.reshape(self.m * 4)
        load_tolist = load.tolist()  # 一维列表

        # 设置上下限
        # 0购电 1购气 2购热 3上网  4cchp供电[0] 5gp供热耗电[1]  6gp供冷耗电[1] 7ap供冷耗电[2] 8gb耗气[3] 9ar耗气[4] 10eb耗电[5]
        # 11蓄热[8] 12放热[9] 13冷水机[6] 14蓄冷[6] 15释冷[7] 16充电[10] 17放电[10] 18风机[11] 19光伏[12]
        # soc:20电池[13] 21水箱[14] 22冰槽[15]
        ub = [cplex.infinity, cplex.infinity, cplex.infinity, 0,
              x[0], x[1], x[1], x[2], x[3], x[4], x[5], x[8], x[9],
              x[6] / self.eru.ice_effect if self.eru else 0, x[6], x[7], x[10], x[10],
              x[11], x[12], x[13], x[14], x[15]]
        ub[0] = 0 if self.config.model_which == 0 else cplex.infinity
        ub[1] = 0 if self.config.model_which == 0 else cplex.infinity
        ub[2] = 0 if self.config.model_which == 0 else self.config.heat_max_power
        ub[3] = cplex.infinity if self.config.model_which == 2 else 0
        lb = np.zeros(self.n)
        ubs = []
        lbs = []
        for t in self.set_T:
            if self.eru and not self.config.hp_is_selected and not self.config.ar_is_selected:
                if self.epr[t - 1] == lp and self.lc[t - 1] == 0:
                    ub[14] = x[6]
                    ub[15] = 0
                else:
                    ub[14] = 0
                    ub[15] = x[7]
            else:
                if self.epr[t - 1] == lp:
                    ub[14] = x[6]
                    ub[15] = 0
                else:
                    ub[14] = 0
                    ub[15] = x[7]
            ubs.append(ub.copy())
            lbs.append(lb.copy())

        ubs_array = np.array(ubs)
        lbs_array = np.array(lbs)

        ubs_list = ubs_array.reshape(self.n * self.m).tolist()
        lbs_list = lbs_array.reshape(self.n * self.m).tolist()

        # 相邻时刻的储能状态关系
        E_names = []
        for t in range(2, self.m + 1):
            Ee = 'Ee' + str(t - 1)
            E_names.append(Ee)
            Eh = 'Eh' + str(t - 1)
            E_names.append(Eh)
            Ec = 'Eh' + str(t - 1)
            E_names.append(Ec)
        E_sense = ['E'] * (self.m - 1) * 3
        E_rhs = [0] * (self.m - 1) * 3
        ef = 0.99  # 充放损失效率
        e = 0.99  # 自损耗
        E_e = [ef, -1 / ef, e, -1]
        E_h = [ef, -1 / ef, e, -1]
        E_c = [ef, -1 / ef, e, -1]
        E_rows = []
        for t in range(2, self.m + 1):
            E_re = [x_vars[t - 2][16], x_vars[t - 2][17], x_vars[t - 2][20], x_vars[t - 1][20]]
            ERE = [E_re, E_e.copy()]
            E_rows.append(ERE)
            E_rh = [x_vars[t - 2][11], x_vars[t - 2][12], x_vars[t - 2][21], x_vars[t - 1][21]]
            ERH = [E_rh, E_h.copy()]
            E_rows.append(ERH)
            E_rc = [x_vars[t - 2][14], x_vars[t - 2][15], x_vars[t - 2][22], x_vars[t - 1][22]]
            ERC = [E_rc, E_c.copy()]
            E_rows.append(ERC)

        # 储能始末状态 E_T = E_0
        ch_names_0 = []
        for i in range(self.d):
            for j in range(2):
                che = 'che_' + str(i) + '_' + str(j)
                ch_names_0.append(che)
                chh = 'chh_' + str(i) + '_' + str(j)
                ch_names_0.append(chh)
                chc = 'chc_' + str(i) + '_' + str(j)
                ch_names_0.append(chc)
        ch_rhs_0 = [0] * self.d * 2 * 3
        ch_sense_0 = ['E'] * self.d * 2 * 3
        ch_rows_0 = []
        for i in range(self.d):
            rowe = [[x_vars[i * 24][20]], [1]]
            ch_rows_0.append(rowe)
            rowh = [[x_vars[i * 24][21]], [1]]
            ch_rows_0.append(rowh)
            rowc = [[x_vars[i * 24][22]], [1]]
            ch_rows_0.append(rowc)
            rowen = [[x_vars[i * 24 + 23][20]], [1]]
            ch_rows_0.append(rowen)
            rowhn = [[x_vars[i * 24 + 23][21]], [1]]
            ch_rows_0.append(rowhn)
            rowcn = [[x_vars[i * 24 + 23][22]], [1]]
            ch_rows_0.append(rowcn)

        # 冷机出力与蓄冰关系
        un_names = []
        for t in self.set_T:
            if self.eru and not self.config.hp_is_selected and not self.config.ar_is_selected:
                if self.epr[t - 1] == lp and self.lc[t - 1] == 0:
                    un_c = 'un_c' + str(t)
                    un_names.append(un_c)
            elif self.eru:
                if self.epr[t - 1] == lp:
                    un_c = 'un_c' + str(t)
                    un_names.append(un_c)
            else:
                pass

        un_sense = []
        for t in self.set_T:
            if self.eru and not self.config.hp_is_selected and not self.config.ar_is_selected:
                if self.epr[t - 1] == lp and self.lc[t - 1] == 0:
                    uch = 'E'
                    un_sense.append(uch)
            elif self.eru:
                if self.epr[t - 1] == lp:
                    uch = 'E'
                    un_sense.append(uch)
            else:
                pass

        un_rows = []
        un_c = [self.eru.ice_effect if self.eru else 0, -1 if self.eru else 0]
        for t in self.set_T:
            if self.eru and not self.config.hp_is_selected and not self.config.ar_is_selected:
                if self.epr[t - 1] == lp and self.lc[t - 1] == 0:
                    un_rc = [x_vars[t - 1][13], x_vars[t - 1][14]]
                    unc = [un_rc, un_c.copy()]
                    un_rows.append(unc)
            elif self.eru:
                if self.epr[t - 1] == lp:
                    un_rc = [x_vars[t - 1][13], x_vars[t - 1][14]]
                    unc = [un_rc, un_c.copy()]
                    un_rows.append(unc)
            else:
                pass

        un_rhs = [0] * len(un_rows)

        # 设置求最小值
        self.prob.objective.set_sense(self.prob.objective.sense.minimize)
        # 设置变量
        self.prob.variables.add(obj=obj_list, ub=ubs_list, lb=lbs_list, names=var)
        # 设置等式约束
        self.prob.linear_constraints.add(lin_expr=rows, senses=my_sense, rhs=load_tolist, names=my_row_names)
        # 储能约束
        self.prob.linear_constraints.add(lin_expr=E_rows, senses=E_sense, rhs=E_rhs, names=E_names)
        self.prob.linear_constraints.add(lin_expr=ch_rows_0, senses=ch_sense_0, rhs=ch_rhs_0, names=ch_names_0)
        self.prob.linear_constraints.add(lin_expr=un_rows, senses=un_sense, rhs=un_rhs, names=un_names)
        try:
            self.prob.set_log_stream(None)
            self.prob.set_error_stream(None)
            self.prob.set_warning_stream(None)
            self.prob.solve()  # 求解
            self.op_value = self.prob.solution.get_objective_value()  # 燃料成本
            self.res = self.prob.solution.get_values()  # 运行参数
            self.result = np.array(self.res).reshape(self.m, self.n)
        except cplex.exceptions.errors.CplexSolverError as e:
            print('无解')
            print(e)
            self.op_value = float(1e10)
        self.prob.variables.delete()
        self.prob.linear_constraints.delete()
        return self.op_value

    def fitness(self, x):
        # 计算投资
        if self.config.opt_target == 0:
            self.invest = np.dot(self.p_invest, x) * (self.R * (1 - self.v) + self.i * self.v)
        else:
            self.invest = 0
        # 燃料成本
        self.op_value = self.opt(x)
        # 计算适应度
        self.fitness_value = self.op_value + self.invest
        return self.fitness_value

    def iterator(self):
        # flag = False
        for step in range(self.max_iter):
            self.iter += 1
            for i in range(self.pop):
                # 更新速度
                self.V[i] = self.nsw * self.V[i] + self.c1 * random.uniform(0, 1) * (self.p_best[i] - self.X[i]) + (
                        self.c2 * random.uniform(0, 1) * (self.g_best - self.X[i]))
                # 判断速度是否出界
                for j in range(self.dim):
                    if self.V[i][j] > self.v_max[j]:
                        self.V[i][j] = self.v_max[j]
                    elif self.V[i][j] < self.v_min[j]:
                        self.V[i][j] = self.v_min[j]
                    else:
                        pass
                # 更新位置
                self.X[i] = self.X[i] + self.V[i]
                # 判断位置是否出界
                for k in range(self.dim):
                    if self.X[i][k] > self.x_max[k]:
                        self.X[i][k] = self.x_max[k]
                    elif self.X[i][k] < self.x_min[k]:
                        self.X[i][k] = self.x_min[k]
                    else:
                        pass
                temp = self.fitness(self.X[i])  # 更新当前粒子适应度
                if temp < self.p_fit[i]:
                    self.p_best[i] = self.X[i]  # 更新个体最优位置
                    self.p_fit[i] = temp.copy()  # 更新个体最优适应度
                if temp < self.fit:
                    self.g_best = self.X[i]  # 更新全局最优位置
                    # self.ref = self.fit     # 记录更新前的适应度
                    self.fit = temp.copy()  # 更新全局适应度
                # else:
                #     self.fit_kp += 1
            # for i in range(self.pop-1):
            #     if self.X[i] == self.X[i+1]:
            #         self.itr_ct += 1
            #         if self.itr_ct == self.pop - 1:
            #             flag = True
            #     else:
            #         self.itr_ct = 0

            self.pro = (0.1 + (step / self.max_iter) / 1.25) * 100
            self.prc[0], self.prc[1] = self.pro, '第' + str(step + 1) + '次迭代, 当前适应度：' + str(int(self.fit))
            self.fit_list.append(int(self.fit))
            self.prc[2] = self.fit_list

            # self.error = 100 * (self.fit - self.ref) / self.fit  # 计算残差
            # if self.error < self.config.residual:  # 是否小于收敛残差
            #     self.err_ct += 1
            #     if self.err_ct > 9:
            #         flag = True
            #         self.prc[0] = 90
            #         self.prc[1] = '终止迭代'
            #         break
            # else:
            #     self.err_ct = 0
            # if flag:
            #     break
            print('===============当前最优适应度==============', self.fit)
            print('---------------当前最优位置----------------', self.g_best)
            print('第', (step + 1), '次迭代')
        print('****************Finished********************')
        return self.fit


def save_cal_data(x: PSO):  # 保存计算结果
    target_file_path = os.path.join(STATIC_ROOT + '/business/user_params_file/', x.who + "_result_" + x.mark + ".xlsx")
    # 将结果模板excel拷贝后生成出属于用户本次规划的一份
    target_path = STATIC_ROOT + '/business/params_file/' + 'result_temp.xlsx'
    book = openpyxl.load_workbook(target_path)
    target_sheet = book.worksheets[0]
    cal_results = []
    for i in range(x.m):
        if x.eru and not x.config.hp_is_selected and not x.config.ar_is_selected:
            if x.epr[i] == min(x.ele_pri) and x.lc[i] == 0:
                eru_effect = x.eru.ice_effect
            else:
                eru_effect = x.eru.cool_effect
        elif x.eru:
            if x.epr[i] == min(x.ele_pri):
                eru_effect = x.eru.ice_effect
            else:
                eru_effect = x.eru.cool_effect
        else:
            eru_effect = 0
        rst = {
            'design_id': x.config.design_id,
            'target_date': x.date[i],
            'target_time': x.hour[i],
            'ele_buy': int(x.result[i][0]),
            'ele_pri': int(x.result[i][0] * x.epr[i]),
            'gas_buy': int(x.result[i][1]),
            'gas_pri': int(x.result[i][1] * x.gpr[i]),
            'heat_buy': int(x.result[i][2]),
            'heat_pri': int(x.result[i][2] * x.hpr[i]),
            'ele_sell': int(x.result[i][3]),
            'ele_inc': int(x.result[i][3] * x.psn[i]),
            'ice_os': int(x.result[i][4]),
            'ice_ig': int(x.result[i][4] * x.g5),
            'cchp_oh': int(x.result[i][4] * x.h5) if x.lh[i] > 0 and x.lc[i] == 0 else 0,
            'cchp_oc': int(x.result[i][4] * x.c5) if x.lc[i] > 0 else 0,
            'gp_ieh': int(x.result[i][5]),
            'gp_iec': int(x.result[i][6]),
            'gp_oh': int(x.result[i][5] * x.gp.make_heat_effect if x.gp else 0),
            'gp_oc': int(x.result[i][6] * x.gp.make_cool_effect if x.gp else 0),
            'ap_ie': int(x.result[i][7]),
            'ap_oc': int(x.result[i][7] * x.ap.effect if x.ap else 0),
            'gb_ig': int(x.result[i][8]),
            'gb_oh': int(x.result[i][8] * x.gb.heat_effect / 100 if x.gb else 0),
            'ar_ig': int(x.result[i][9]),
            'ar_oc': int(x.result[i][9] * x.ar.cool_effect if x.ar else 0),
            'eb_ie': int(x.result[i][10]),
            'eb_oh': int(x.result[i][10] * x.eb.heat_effect / 100 if x.eb else 0),
            'heat_store': int(x.result[i][11]),
            'heat_release': int(x.result[i][12]),
            'eru_ie': int(x.result[i][13]),
            'eru_oc': int(x.result[i][13] * eru_effect),
            'it_ch': int(x.result[i][14]),
            'it_dis': int(x.result[i][15]),
            'ele_ch': int(x.result[i][16]),
            'ele_dis': int(x.result[i][17]),
            'wind': int(x.result[i][18]),
            'PV': int(x.result[i][19]),
            'wind_power': int(x.result[i][18] * getattr(x.wind_power, 's' + str(int(x.wind[i])))
                              if 2 <= x.wind[i] <= 25 and x.wind_power else 0),
            'pvp_power': int(x.result[i][19] * x.pv[i] * x.pvp.max_power / 1000000 if x.pvp else 0),
            'ele_soc': int(x.result[i][20]),
            'heat_soc': int(x.result[i][21]),
            'ice_soc': int(x.result[i][22]),
            'heat_load': int(x.lh[i]),
            'cold_load': int(x.lc[i]),
            'ele_load': int(x.le[i]),
            'gas_load': int(x.lg[i]),
            'sell_h': int(x.lh[i] * x.heat_sell[i]),
            'sell_c': int(x.lc[i] * x.cool_sell[i]),
            'sell_e': int(x.le[i] * x.ele_sell[i]),
            'sell_g': int(x.lg[i] * x.gas_sell[i]),
            'co2_em': int(x.result[i][1] * 0.589),
            'nox_em': int(x.result[i][1] * 0.000378),
        }  # 对应计算excel里的结果
        cal_results.append(CalResult(**rst))
        target_sheet.append(tuple(rst.values()))
    CalResult.objects.bulk_create(cal_results)

    CalResult.objects.filter(target_time=0).update(is_correct=False, error_msg="123")
    book.save(target_file_path)

    config_limit = {
        "design_id": x.config.design_id,
        "cchp": x.g_best[0],
        "gp": x.g_best[1],
        "ap": x.g_best[2],
        "gb": x.g_best[3],
        "ar": x.g_best[4],
        "eb": x.g_best[5],
        "ice_ch": x.g_best[6],
        "ice_dis": x.g_best[7],
        "heat_ch": x.g_best[8],
        "heat_dis": x.g_best[9],
        "ele_ch": x.g_best[10],
        "battery": x.g_best[13],
        "wind": x.g_best[11],
        "pvp": x.g_best[12],
        "heat_store": x.g_best[14],
        "ice_tank": x.g_best[15],
    }
    ConfigLimit(**config_limit).save()


def fill_economic(d_id, light_out, wind_out, ecom_data, who):
    """
    将计算好的数据填入经评文件中
    :param d_id:
    :param light_out:
    :param wind_out:
    :param ecom_data:
    :param who:
    :return:
    """

    # 定义data对象，用于保存数据与excel的对应关系
    data = {
        # 来自文件
        "loan_ratio": (0, 'B5'),
        "capital_ratio": (0, 'B6'),
        "lending_rates": (0, 'B7'),
        "liquidity_ratio": (0, 'B10'),
        "depreciation_period": (0, 'B11'),
        "residual_rate": (0, 'B12'),
        "apia": (0, 'B13'),
        "reserve_rate": (0, 'B14'),
        "e_vat": (0, 'B26'),
        "hc_vat": (0, 'B27'),
        "cgccf": (0, 'B34'),
        "cgcepf": (0, 'B35'),
        "cgcof": (0, 'B37'),
        "scesf": (0, 'B41'),
        "cgcief": (0, 'B36'),
        # 来自求和
        "ice_os": (1, 'D5'),  # 三联供发电
        'ele_buy': (1, 'D6'),  # 购电
        'gb_oh': (1, 'D7'),  # 燃气锅炉供热
        'gp_oh': (1, 'D8'),  # 地源热泵供热
        'eb_oh': (1, 'D9'),  # 电锅炉供热
        'cchp_oh': (1, 'D11'),  # 三联供供热
        'heat_buy': (1, 'D13'),  # 购热
        'gp_oc': (1, 'D15'),  # 地源热泵供冷
        'ap_oc': (1, 'D16'),  # 风冷热泵供冷
        'cchp_oc': (1, 'D17'),  ##三联供供冷
        'it_dis': (1, 'D18'),  # 冰蓄冷
        'ar_oc': (1, 'D14'),  # 直燃型制冷机供冷
        'ele_load': (1, 'B20'),  # 电负荷
        'heat_load': (1, 'B21'),  # 热负荷
        'cold_load': (1, 'B22'),  # 冷负荷
        'gas_buy': (1, 'B26'),  # 购气

        'gb_ig': (8, 'D7'),
        'eb_ie': (8, 'D8'),
        'it_ch': (8, 'D9'),
        'ar_ig': (8, 'D10'),
        'gp_ieh': (8, 'D11'),
        'ap_ie': (8, 'D12'),
        'ele_pri': (8, 'D17'),
        'heat_pri': (8, 'D18'),
        # 来自计算结果
        'light_output': (1, 'C2'),  # 光伏发电量
        'wind_output': (1, 'C3'),  # 风力发电量
        # 最大值结果（根据额定输入功率计算设备投资）
        'pvp_max': (0, 'G4'),  # 光伏价格
        'wind_max': (0, 'G5'),  # 风机投资
        'gb_max': (0, 'G6'),  # 燃气锅炉投资
        'eb_max': (0, 'G7'),  # 电锅炉投资
        'eru_max': (0, 'G8'),  # 冷水机组投资
        'ar_max': (0, 'G9'),  # 直燃型制冷机投资
        'gp_max': (0, 'G10'),  # 地源热泵投资
        'ap_max': (0, 'G11'),  # 风冷热泵投资
        'ICE_max': (0, 'G12'),  # 内燃机系统投资
        'gt_max': (0, 'G13'),  # 燃气轮机系统投资
    }

    sum_data = CalResult.objects.filter(design_id=d_id).aggregate(ice_os=Sum("ice_os"), ele_buy=Sum('ele_buy'),
                                                                  gb_oh=Sum('gb_oh'),
                                                                  eb_oh=Sum('eb_oh'), cchp_oh=Sum('cchp_oh'),
                                                                  heat_buy=Sum('heat_buy'),
                                                                  gp_oc=Sum('gp_oc'), ap_oc=Sum('ap_oc'),
                                                                  cchp_oc=Sum('cchp_oc'),
                                                                  it_dis=Sum('it_dis'), ar_oc=Sum('ar_oc'),
                                                                  ele_load=Sum('ele_load'),
                                                                  heat_load=Sum('heat_load'),
                                                                  cold_load=Sum('cold_load'),
                                                                  gas_buy=Sum('gas_buy'), gp_oh=Sum('gp_oh'),
                                                                  gb_ig=Sum('gb_ig'),
                                                                  eb_ie=Sum('eb_ie'), it_ch=Sum('it_ch'),
                                                                  ar_ig=Sum('ar_ig'),
                                                                  gp_ieh=Sum('gp_ieh'), ap_ie=Sum('ap_ie'),
                                                                  ele_pri=Sum('ele_pri'),
                                                                  heat_pri=Sum('heat_pri'))

    max_data = CalResult.objects.filter(design_id=d_id).aggregate(pvp_max=Max('pvp_power'), wind_max=Max('wind_power'),
                                                                  gb_max=Max('gb_oh'),
                                                                  eb_max=Max('eb_oh'), eru_max=Max('it_dis'),
                                                                  ar_max=Max('ar_oc'),
                                                                  gp_max=Max('gp_oh'), ap_max=Max('ap_oc'),
                                                                  ICE_max=Max('ice_os'))

    design = DesignPlan.objects.values('summer_day', 'winter_day', 'gt_which',
                                       'ice_which', 'gp_which', 'ap_which', 'gb_which', 'ar_which', 'eb_which',
                                       'cu_which',
                                       'wind_which', 'solar_which', 'cu_which', 'ar_which', 'ice_which').get(
        belongs__account=who,
        design_id=d_id)

    target_path = STATIC_ROOT + '/business/params_file/' + 'fs_template.xlsx'
    copy_path = STATIC_ROOT + '/business/user_params_file/' + who + "_economic_" + d_id + '.xlsx'

    book = openpyxl.load_workbook(target_path)

    # 将由规划出的结果有关经评的数据填入sheet中

    for k, v in ecom_data.items():
        sheet, loc = data[k]
        book.worksheets[sheet][loc].value = v

    # 将需要求和的结果填入结果中
    for k, v in sum_data.items():
        sheet, loc = data[k]
        book.worksheets[sheet][loc].value = v

    # 以下为最大出力 * 设备的单价（ppp:price per power)
    sheet, loc = data['wind_max']
    if design['wind_which']:
        book.worksheets[sheet][loc].value = WindPower.objects.get(model_name=design['wind_which']).ppp * max_data[
            'pvp_max']
    else:
        book.worksheets[sheet][loc].value = 0

    sheet, loc = data['pvp_max']
    if design['solar_which']:
        book.worksheets[sheet][loc].value = PVP.objects.get(model_name=design['solar_which']).ppp * max_data['pvp_max']
    else:
        book.worksheets[sheet][loc].value = 0

    sheet, loc = data['gb_max']
    if design['gb_which']:
        book.worksheets[sheet][loc].value = GasBoiler.objects.get(model_name=design['gb_which']).ppp * max_data[
            'gb_max']
    else:
        book.worksheets[sheet][loc].value = 0

    sheet, loc = data['eb_max']
    if design['eb_which']:
        book.worksheets[sheet][loc].value = ElectricBoiler.objects.get(model_name=design['eb_which']).ppp * max_data[
            'eb_max']
    else:
        book.worksheets[sheet][loc].value = 0

    sheet, loc = data['eru_max']
    if design['cu_which']:
        book.worksheets[sheet][loc].value = ERU.objects.get(model_name=design['cu_which']).ppp * max_data['eru_max']
    else:
        book.worksheets[sheet][loc].value = 0

    sheet, loc = data['ar_max']
    if design['ar_which']:
        book.worksheets[sheet][loc].value = AR.objects.get(model_name=design['ar_which']).ppp * max_data['ar_max']
    else:
        book.worksheets[sheet][loc].value = 0

    sheet, loc = data['gp_max']
    if design['gp_which']:
        book.worksheets[sheet][loc].value = GroundPump.objects.get(model_name=design['gp_which']).ppp * max_data[
            'gp_max']
    else:
        book.worksheets[sheet][loc].value = 0

    sheet, loc = data['ap_max']
    if design['ar_which']:
        book.worksheets[sheet][loc].value = AR.objects.get(model_name=design['ar_which']).ppp * max_data['ap_max']
    else:
        book.worksheets[sheet][loc].value = 0

    sheet, loc = data['ICE_max']
    if design['ice_which']:
        book.worksheets[sheet][loc].value = ICE.objects.get(model_name=design['ice_which']).ppp * max_data['ICE_max']
    else:
        book.worksheets[sheet][loc].value = 0

    sheet, loc = data['gt_max']
    if design['gt_which']:
        book.worksheets[sheet][loc].value = GasTurbine.objects.get(model_name=design['gt_which']).ppp * max_data[
            'ICE_max']
    else:
        book.worksheets[sheet][loc].value = 0
    sheet1 = book.worksheets[0]
    sheet1['C2'].value = light_out
    sheet1['C3'].value = wind_out
    # 数据保存
    book.save(copy_path)


class CalThread(threading.Thread):
    def __init__(self, mark, who, eco):
        self.mark = mark
        self.who = who
        self.eco = eco
        super(CalThread, self).__init__()
        self.pro = [0, '', []]

    def run(self):
        temp_log_dir = STATIC_ROOT + '/business/temp_log/'
        file_path = os.path.join(STATIC_ROOT + '/business/temp_log/', self.who + "_log_" + self.mark + ".log")
        try:
            self.pro[0] = 5
            self.pro[1] = "处理配置..."
            cal_config = DesignPlan.objects.get(design_id=self.mark)
            pso = PSO(cal_config, self.mark, self.who, self.pro)
            self.pro[0] = 10
            self.pro[1] = "初始化..."
            pso.init_pop()
            if pso.nsl_count == pso.pop:
                if not os.path.exists(temp_log_dir):
                    os.mkdir(temp_log_dir)
                result_path = os.path.join(STATIC_ROOT + '/business/temp_log/',
                                           pso.who + "_log_" + pso.mark + ".log")
                pso.prob.set_results_stream(open(result_path, mode="w"))
                pso.max_iter = 1
            self.pro[1] = "当前适应度：" + str(int(pso.fit)) + "   开始迭代..."
            pso.iterator()
            self.pro[0], self.pro[1] = 90, "保存中..."
            save_cal_data(pso)
            self.pro[0], self.pro[1] = 95, "保存经评文件..."
            fill_economic(d_id=self.mark, who=self.who, light_out="", wind_out="", ecom_data=self.eco)
            self.pro[0], self.pro[1] = 100, "计算完成..."
        except Exception as e:
            if os.path.exists(file_path):
                with open(file_path, "r") as f:
                    error_msg = f.read()
                    CalResult(design_id=self.mark, is_correct=False, error_msg=str(error_msg)).save()
            else:
                CalResult(design_id=self.mark, is_correct=False, error_msg=str(e)).save()
            self.pro[0], self.pro[1] = 400, str(e)

    def get_result(self):
        return self.pro[0], self.pro[1], self.pro[2]
